const mysql = require('mysql');
module.exports = {
    /** 查询是否存在记录 */
    async exists(ctx, tabName, whereObj) {
        let sql = `SELECT  COUNT(1) as count FROM ${tabName}`;
        let whereStr = ' 1=1';
        if (whereObj && JSON.stringify(whereObj).replace(/ /g, '').length > 0) {
            for (const key in whereObj) {
                whereStr += ` and ${key}=${mysql.escape(whereObj[key])}`;
            }
            sql += ` where ${whereStr}`;
        }

        let data = await ctx.db.query(sql);
        if (data && data[0].count > 0) {
            return true;
        } else {
            return false;
        }
    },

    /** 查询单条记录 */
    async queryone(ctx, tabName, selectStr, whereObj) {
        let sql = `SELECT ${selectStr} FROM ${tabName}`;
        let whereStr = '1=1';
        if (whereObj && JSON.stringify(whereObj).replace(/ /g, '').length > 0) {
            for (const key in whereObj) {
                whereStr += ` and ${key}=${mysql.escape(whereObj[key])}`;
            }
            sql += ` where ${whereStr}`;
        }

        let data = await ctx.db.query(sql);
        return data.length > 0 ? data[0] : null;
    },

    /** 查询数据集 */
    async querylist(ctx, tabName, selectStr, whereObj, orderStr) {
        let sql = `SELECT ${selectStr} FROM ${tabName}`;
        if (orderStr && orderStr.replace(/ /g, '').length > 0) {
            sql += ` order by ${orderStr}`;
        }
        let whereStr = '1=1';
        if (whereObj && JSON.stringify(whereObj).replace(/ /g, '').length > 0) {
            for (const key in whereObj) {
                whereStr += ` and ${key}=${mysql.escape(whereObj[key])}`;
            }
            sql += ` where ${whereStr}`;
        }

        return ctx.db.query(sql);
    },

    /** 分页查询数据集 */
    async queryPage(ctx, tabName, selectStr, whereObj, page = 1, pageSize = 10) {
        let sql = `SELECT COUNT(1) AS total FROM ${tabName} `;
        let sql2 = `SELECT ${selectStr} FROM ${tabName} `;
        let whereStr = '1=1';
        if (whereObj && JSON.stringify(whereObj).replace(/ /g, '').length > 0) {
            for (const key in whereObj) {
                whereStr += ` and ${key}=${mysql.escape(whereObj[key])}`;
            }
            sql += ` where ${whereStr}`;
            sql2 += ` where ${whereStr}`;
        }
        sql2 += ` LIMIT ${(page - 1) * pageSize}, ${pageSize}`;
        let total = await ctx.db.query(sql);
        let data = await ctx.db.query(sql2);
        return {
            rows: data,
            total: total[0].total,
            curPage: +page,
            pageSize: +pageSize
        }
    },

    /** 执行sql语句 */
    async querysql(ctx, sqlStr) {
        return ctx.db.query(sqlStr);
    },

    /** 根据id查询单条记录 */
    async queryById(ctx, tabName, id) {
        let sql=`SELECT * FROM ${tabName} WHERE id=${id}`;
        console.log(sql);
        let data = await ctx.db.query(sql);
        return data.length > 0 ? data[0] : null;
    },

    /** 获取记录条数 */
    async queryCount(ctx, tabName, whereStr) {
        let sql = `SELECT count(1) as total FROM ${tabName}`;
        if (whereStr && whereStr.replace(/ /g, '').length > 0) {
            sql += ` where ${whereStr}`;
        }

        let data = await ctx.db.query(sql);
        return data[0].total;
    },
}